﻿using System.Collections.Generic;
using System.Linq;
using WMSSyncData.Model;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.SqlDB
{
    public class OutStockMDAL : BaseSqlDBDAL<tddev112>
    {
        public List<tddev112> GetSyncData()
        {
            var sql = @"SELECT os.BillNo AS wmso,os.RelatedBill AS orno,sr.RowNo AS pono,sr.SN AS seqn ,sr.ProductNumber AS item,' ' clot,sr.SelectQty AS qoro, 
  '1'AS host,' ' AS mess ,GETDATE() AS edte1,GETDATE() AS wdte1,GETDATE() AS wdte2,CASE WHEN LEFT(os.RelatedBill,3)='RET' THEN '1' ELSE '2'END AS reto,0 AS refcntu,0 AS refcntd,
case when isnull(Ext3,'')='' then ' ' else Ext3 end AS wrhs
  FROM [dbo].[wms_T_OutStock] os
  LEFT JOIN [dbo].[wms_T_OutStockRows] sr ON os.BillNo=sr.BillNo 
  WHERE os.Status='1' AND os.BillType<>'NG返工出库单' AND os.BillType<>'返工出库单' AND os.BillType<>'手工出库单' AND os.BillType<>'销售计划' AND os.BillType<>'报废出库单'";
            var result = SqlDBHelper.GetDataSet(sql).Tables[0];
            var list = MakeTablePackage(result);
            return list;
        }
        //public List<tddev112> GetSHXSDate(string orno, string pono, string sqnb, double qoro)
        //{
        //    List<tddev112> result = new List<tddev112>();
        //    var sql = string.Format("select * from  ttddev997view where HY_SO='{0}' and HY_SO_LINE='{1}' and HY_SO_SQNB='{2}'", orno, pono, sqnb);
        //    var dtview = OracleDBHelper.GetDataSet(sql).Tables[0];
        //    var getdate = DateTime.Now.ToString();
        //    if (dtview.Rows.Count > 0)
        //    {
        //        var sql111 = string.Format(
        //            "select t$orno as orno,t$pono as pono,t$sqnb as seqn,t$item as item,{3} as qoro,' ' as clot,'1'AS host,' ' AS mess ,'{4}'AS edte1,'{4}' AS wdte1,'{4}' AS wdte2,CASE WHEN substr(t$orno,1,3)='HRT' THEN '1' ELSE '2'END AS reto,0 AS refcntu,0 AS refcntd from ttddev111101 where t$orno='{0}' and t$pono='{1}' and t$sqnb='{2}'", dtview.Rows[0]["SH_SO"], dtview.Rows[0]["SH_SO_LINE"], dtview.Rows[0]["SH_SO_SQNB"], qoro,getdate);
        //        var dt = OracleDBHelper.GetDataSet(sql111).Tables[0];
        //        result = MakeTablePackage(dt);
        //        return result;
        //    }
        //    else
        //    {
        //        return result;
        //    }

        //}
        //public bool UpdateUploadStatus(List<tddev112> list)
        //{
        //    var result = false;
        //    //var billNoList = list.Select(x => x.orno).Distinct();
        //    if (list.Count() > 0)
        //    {
        //        var sqlWhere = "where ";
        //        foreach (var item in list)
        //        {
        //            sqlWhere = sqlWhere + " ( RelatedBill='" + item.orno + "' and  sn=" + item.seqn + " and rowno=" + item.pono + ") or";
        //        }
        //        sqlWhere = sqlWhere.TrimEnd('r');
        //        sqlWhere = sqlWhere.TrimEnd('o');

        //        var sql = "UPDATE  wms_T_OutStock SET Status='2' " + sqlWhere;
        //        var updateRes = SqlDBHelper.ExcuteSQL(sql);
        //        if (updateRes > 0)
        //        {
        //            result = true;
        //        }
        //    }
        //    return result;

        //}

        public bool UpdateUploadStatus(tddev112 tddev)
        {
            var result = false;

            var sql = "UPDATE  wms_T_OutStock SET Status='2' where BillNo='{0}'";
            sql = string.Format(sql, tddev.wmso);
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            if (updateRes > 0)
            {
                result = true;
            }
            return result;

        }

        /// <summary>
        /// 将订单类型为寄售出库且数量为负数的单子改为寄售退货
        /// </summary>
        /// <returns></returns>
        public int UpdateBillTypeForVMI()
        {
            var sql = @"update wms_T_OutStock set BillType='寄售退货' where BillNo in(select a.BillNo  from wms_T_OutStockRows a LEFT JOIN wms_T_OutStock b on a.BillNo=b.BillNo where PlanQty<0  and b.BillType='寄售出库'  and b.Status='0')";
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            return updateRes;
        }
    }
}
